from django.db import models

# Create your models here.

class Book(models.Model):
    title = models.CharField('书名',max_length=50,default='',unique=True)
    pub = models.CharField('出版社',max_length=100,default='')
    price = models.DecimalField('价格',max_digits=7,decimal_places=2)
    market_price = models.DecimalField('零售价', max_digits=7,decimal_places=2,default=0.0)
    is_active = models.BooleanField('是否活跃',default=True)

    class Meta:
        db_table = 'book'
        verbose_name = '图书'
        verbose_name_plural = verbose_name
    def __str__(self):
        return '%s_%s_%s_%s'%(self.title,self.pub,self.price,self.market_price)


class Author(models.Model):
    name = models.CharField('姓名',max_length=11)
    age = models.IntegerField('年龄',default=1)
    email = models.EmailField('邮箱',null=True)
    class Meta:
        db_table = 'author'

#python manage.py shell
# >>> from bookstore.models import Book
# >>> Book.objects.create(title='python',pub='清华大学出版社',price=20,market_price=25)
# <Book: Book object (1)>
# >>> b2=Book(title='Django',pub='清华大学出版社',price=70,market_price=75)
# >>> b2.save()

# D:\PyCharm\pycharm_project\new\mysite1>python manage.py shell
# Python 3.5.3 (v3.5.3:1880cb95a742, Jan 16 2017, 16:02:32) [MSC v.1900 64 bit (AMD64)] on win32
# Type "help", "copyright", "credits" or "license" for more information.
# (InteractiveConsole)
# >>> from bookstore.models import Book
# >>> a1=Book.objects.all()
# >>> a1
# <QuerySet [<Book: python_清华大学出版社_20.00_25.00>, <Book: Django_清华大学出版社_70.00_75.00>, <Book: JQuery_机械工业出版社_90.00_85.00>, <Book:
# Linux_机械工业出版社_80.00_65.00>, <Book: HTML5_清华大学出版社_90.00_105.00>]>

# >>> a2=Book.objects.values('title','pub')
# >>> a2
# <QuerySet [{'title': 'python', 'pub': '清华大学出版社'}, {'title': 'Django', 'pub': '清华大学出版社'}, {'title': 'JQuery', 'pub': '机械工业出版社'}
# , {'title': 'Linux', 'pub': '机械工业出版社'}, {'title': 'HTML5', 'pub': '清华大学出版社'}]>
# >>> for book in a2:
# ...     print(book['title'])
# ...
# python
# Django
# JQuery
# Linux
# HTML5
# >>>

# >>> a3=Book.objects.values_list('title','pub')
# >>> a3
# <QuerySet [('python', '清华大学出版社'), ('Django', '清华大学出版社')
# , ('JQuery', '机械工业出版社'), ('Linux', '机械工业出版社'), ('HTML5'
# , '清华大学出版社')]>
# >>> for book in a3:
# ...     print(book[0])
# ...
# python
# Django
# JQuery
# Linux
# HTML5
# >>>

# >>> a4=Book.objects.order_by('-price')
# >>> a4
# <QuerySet [<Book: JQuery_机械工业出版社_90.00_85.00>, <Book: HTML5_清华大学出版社_90.00_105.00>, <Book: Linux_机械工
# 业出版社_80.00_65.00>, <Book: Django_清华大学出版社_70.00_75.00>, <Book: python_清华大学出版社_20.00_25.00>]>
# >>> a5=Book.objects.values('title','price').order_by('-price')
# >>> a5
# <QuerySet [{'price': Decimal('90.00'), 'title': 'JQuery'}, {'price': Decimal('90.00'), 'title': 'HTML5'}, {'price': D
# ecimal('80.00'), 'title': 'Linux'}, {'price': Decimal('70.00'), 'title': 'Django'}, {'price': Decimal('20.00'), 'titl
# e': 'python'}]>
# >>> print(a5.query)
# SELECT `book`.`title`, `book`.`price` FROM `book` ORDER BY `book`.`price` DESC

# >>> from bookstore.models import Book
# >>> b1=Book.objects.filter(pub='清华大学出版社')
# >>> b1
# <QuerySet [<Book: python_清华大学出版社_20.00_25.00>, <Book: Django_清华大学出版社_70.00_75.00>, <Book: HTML5_清华大
# 学出版社_90.00_105.00>]>
# >>> print(b1.query)
# SELECT `book`.`id`, `book`.`title`, `book`.`pub`, `book`.`price`, `book`.`market_price` FROM `book` WHERE `book`.`pub
# ` = 清华大学出版社

#get的查询结果只能有1条数据，为空或者大于1条直接报错；输出结果为一个模型类的实例，非queryset
# >>> b1=Book.objects.get(id=1)
# >>> b1
# <Book: python_清华大学出版社_20.00_25.00>

# exclude:返回不包含此条件的所有数据集
# >>> b1=Book.objects.exclude(pub='清华大学出版社')
# >>> b1
# <QuerySet [<Book: JQuery_机械工业出版社_90.00_85.00>, <Book: Linux_机械工业出版社_80.00_65.00>]>

#__exact:等于；
#   样例：Author.objects.filter(id__exact=1)
#   等同于：select * from author where id = 1;
#__contains:包含指定值
#   样例：Author.objects.filter(name__contains='w')
#   等同于：select * from author where name like "%w%"
#__startswith:以xxx开始
#__endswith:以xxx结束
#__gt：大于指定值
#   样例：Author.objects.filter(age_gt=50)
#   等同于：select * from author where age > 50;
#__gte：大于等于指定值
#__lt：小于指定值
#__lte：小于等于指定值
# __in:查询范围是否在指定范围内；
#   样例：Author.objects.filter(country__in=['中国','日本','韩国'])
#   等同于：select * from author where country in ('中国','日本','韩国')
# __range:查找数据是否在指定的区间范围内
#   样例：Author.objects.filter(age__range(35,50))
#   等同于：select * from author where age between 35 and 50;b1

# 修改单个实体的某些字段值的步骤:1查、2改、3保存
# >>> from bookstore.models import Book
# >>> b1 = Book.objects.get(id=1)
# >>> b1
# <Book: python_清华大学出版社_20.00_25.00>
# >>> b1.price=22
# >>> b1.save()
#直接调用QuerySet的update(属性=值)实现批量修改
# >>> books=Book.objects.filter(pub='清华大学出版社')
# >>> books.update(price=1)
# 3
# >>> bs=Book.objects.filter(pub='清华大学出版社')
# >>> for i in bs:
# ...     print(i.price)
# ...
# 1.00
# 1.00
# 1.00

# F对象和Q对象
# D:\PyCharm\pycharm_project\new\mysite1>python manage.py shell
# Python 3.5.3 (v3.5.3:1880cb95a742, Jan 16 2017, 16:02:32) [MSC v.1900 64 bit (AMD64)] on win32
# Type "help", "copyright", "credits" or "license" for more information.
# (InteractiveConsole)
# >>> from django.db.models import F
# >>> from bookstore.models import Book
# >>> Book.objects.filter(market_price__gt=F('price'))
# <QuerySet [<Book: python_清华大学出版社_10.00_25.00>, <Book: Django_清华大学出版社_1.00_75.00>, <Book: HTML5_清华大学出版社_1.00_105.00>]>
# >>> from django.db.models import Q
# >>> Book.objects.filter(Q(market_price__gt=30)|Q(pub='清华大学出版社'))
# <QuerySet [<Book: python_清华大学出版社_10.00_25.00>, <Book: Django_清华大学出版社_1.00_75.00>, <Book: JQuery_机械工业出版社_90.00_85.00>, <Book: Linux_机械工业出版社_80.00_65.00>, <Book: HTML5_清华大学出版社
# _1.00_105.00>]>
# >>> Book.objects.filter(Q(market_price__gt=F('price'))|Q(pub='机械工业出版社'))
# # <QuerySet [<Book: python_清华大学出版社_10.00_25.00>, <Book: Django_清华大学出版社_1.00_75.00>, <Book: JQuery_机械工业出版社_90.00_85.00>, <Book: Linux_机械工业出版社_80.00_65.00>, <Book: HTML5_清华大学出版社
# # _1.00_105.00>]>


# 聚合查询和原生数据库操作
# 聚合查询-整表聚合
# >>> from django.db.models import Count
# >>> Book.objects.aggregate(res=Count('id'))
# {'res': 5}
# 聚合查询-分组聚合
# >>> bs=Book.objects.values('pub')
# >>> bs.annotate(res=Count('id'))
# <QuerySet [{'pub': '清华大学出版社', 'res': 3}, {'pub': '机械工业出版社', 'res': 2}]>
# >>> bs.annotate(res=Count('id')).filter(res__gt=2)
# <QuerySet [{'pub': '清华大学出版社', 'res': 3}]>
# >>> print(bs.annotate(res=Count('id')).filter(res__gt=2).query)
# SELECT `book`.`pub`, COUNT(`book`.`id`) AS `res` FROM `book` GROUP BY `book`.`pub` HAVING COUNT(`book`.`id`) > 2 ORDER BY NULL

#admin后台管理1
# D:\PyCharm\pycharm_project\new\mysite1>python manage.py createsuperuser
# 用户名 (leave blank to use 'administrator'): administrator
# 电子邮件地址: 1127605495@qq.com
# Password:       !QAZ1qaz
# Password (again):      !QAZ1qaz
# Superuser created successfully.
#访问http://127.0.0.1:8000/admin



